package com.assessment.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;

import com.assessment.entity.FirstPhase;

public class ImportFirstGrade {

	 public void writeExcel03() throws IOException{  
	        //创建工作簿  
	        HSSFWorkbook workBook = new HSSFWorkbook();  
	        //创建工作表  工作表的名字叫helloWorld  
	        HSSFSheet sheet = workBook.createSheet("helloWorld");  
	        //创建行,第3行  
	        HSSFRow row = sheet.createRow(2);  
	        //创建单元格，操作第三行第三列  
	        HSSFCell cell = row.createCell(2, CellType.STRING);  
	        cell.setCellValue("helloWorld");  
	          
	        workBook.write(new File("d:\\poi\\测试.xls"));  
	          
	        workBook.close();//最后记得关闭工作簿  
	    }  
	
	 
	 
	 public List<FirstPhase> importFirstGrade(String path) throws IOException {

	        FileInputStream inputStream = new FileInputStream(new File(path));  
	        //读取工作簿  
	        HSSFWorkbook workBook = new HSSFWorkbook(inputStream);  
	        //读取工作表  
	        HSSFSheet sheet = workBook.getSheetAt(1);  
	        //读取行  
	        List<FirstPhase>list =new ArrayList<FirstPhase>();
	        
	   for (int i = 3; i <sheet.getLastRowNum(); i++) {
	    	  
		   FirstPhase fp=new FirstPhase();
	        
	    	  HSSFRow row = sheet.getRow(i);  
	    	//读取单元格  
	    	  HSSFCell cellstaff = row.getCell(0); 
	    	  HSSFCell cellgk = row.getCell(4); 
	    	  HSSFCell celloa = row.getCell(6); 
	    	  HSSFCell celldy = row.getCell(8); 
	    	  HSSFCell cellcz = row.getCell(10); 
	    	  
	    	  HSSFCell cellly = row.getCell(12); 
	    	  HSSFCell cellzb = row.getCell(14); 
	    	  HSSFCell cell9001 = row.getCell(16); 
	    	  HSSFCell cell27001 = row.getCell(18); 
	    	  HSSFCell cellaq = row.getCell(20); 
	    	  
	    	  HSSFCell cellcmmi = row.getCell(22); 
	    	  HSSFCell cellvss = row.getCell(24); 
	    	  HSSFCell cellword = row.getCell(26); 
	    	  HSSFCell cellexcel = row.getCell(28); 
	    	  HSSFCell cellppt = row.getCell(30); 
	    	  
	    	  HSSFCell cellyanjiang = row.getCell(32); 
	    	  HSSFCell cellqingjia = row.getCell(34); 
	    	  HSSFCell celldiyiming = row.getCell(36); 
	    	  HSSFCell cellgood = row.getCell(37); 
	    	  HSSFCell cellexam = row.getCell(2); 
	    	  HSSFCell cellname = row.getCell(1);
	    	  
	    	  HSSFCell cellevaluation = row.getCell(42); 
	    	  
	    	  cellstaff.setCellType(CellType.STRING);
	    	  cellgk.setCellType(CellType.STRING);
	    	  celloa.setCellType(CellType.STRING);
	    	  celldy.setCellType(CellType.STRING);
	    	  cellcz.setCellType(CellType.STRING);
	    	  cellly.setCellType(CellType.STRING);
	    	  cellzb.setCellType(CellType.STRING);
	    	  cell27001.setCellType(CellType.STRING);
	    	  cell9001.setCellType(CellType.STRING);
	    	  cellaq.setCellType(CellType.STRING);
	    	  cellcmmi.setCellType(CellType.STRING);
	    	  cellvss.setCellType(CellType.STRING);
	    	  cellword.setCellType(CellType.STRING);
	    	  cellexcel.setCellType(CellType.STRING);
	    	  cellppt.setCellType(CellType.STRING);
	    	  cellyanjiang.setCellType(CellType.STRING);
	    	  cellqingjia.setCellType(CellType.STRING);
	    	  celldiyiming.setCellType(CellType.STRING);
	    	  cellgood.setCellType(CellType.STRING);
	    	  cellexam.setCellType(CellType.STRING);
	    	  cellevaluation.setCellType(CellType.STRING);
	    	  cellname.setCellType(CellType.STRING);
	    	  
	    	  
	    	  
	    	  String value = cellstaff.getStringCellValue(); 
	    	  String value1 = cellgk.getStringCellValue();  
	    	  String value2 = celloa.getStringCellValue();  
	    	  String value3 = celldy.getStringCellValue();  
	    	  String value4 = cellcz.getStringCellValue();  
	    	  String value5 = cellly.getStringCellValue();  
	    	  String value6 = cellzb.getStringCellValue(); 
	    	  String value7 = cell9001.getStringCellValue();  
	    	  String value8 = cell27001.getStringCellValue();  
	    	  String value9 = cellaq.getStringCellValue();  
	    	  String value10 = cellcmmi.getStringCellValue();  
	    	  String value11 = cellvss.getStringCellValue();  
	    	  String value12 = cellword.getStringCellValue();  
	    	  String value13 = cellexcel.getStringCellValue();  
	    	  String value14 = cellppt.getStringCellValue();  
	    	  String value15 = cellyanjiang.getStringCellValue();  
	    	  String value16 = cellqingjia.getStringCellValue();  
	    	  String value17 = celldiyiming.getStringCellValue(); 
	    	  String value18 = cellgood.getStringCellValue();  
	    	  String value19 = cellexam.getStringCellValue();  
	    	  String value20 = cellevaluation.getStringCellValue();  
	    	  String value21 = cellname.getStringCellValue();
	    	  
	  
	    	  
	    	  
	//   
	    	fp.setStaffid(Integer.parseInt(value));
	    	fp.setName(value21);
	    	 if(value1.equals("合格")){
	    		  fp.setGaikuang(1); 
	    	 }
	    	 if(value1.equals("不合格")){
	   		  fp.setGaikuang(2); 
	   	 }
	    	 if(value1.equals("假")){
	   		  fp.setGaikuang(3); 
	   	 }
	    	 
	    	 
	    	 
	    	 if(value2.equals("合格")){
	   		  fp.setOa(1); 
	   	 }
	   	 if(value2.equals("不合格")){
	  		  fp.setOa(2); 
	  	 }
	   	 if(value2.equals("假")){
	  		  fp.setOa(3); 
	  	 }
	    	
	   	 
	   	 if(value3.equals("合格")){
	  		  fp.setDayin(1); 
	  	 }
	  	 if(value3.equals("不合格")){
	 		  fp.setDayin(2); 
	 	 }
	  	 if(value3.equals("假")){
	 		  fp.setDayin(3); 
	 	 }  
	  	 
	  	 
	  	 if(value4.equals("合格")){
	 		  fp.setCuozhe(1); 
	 	 }
	 	 if(value4.equals("不合格")){
			  fp.setCuozhe(2); 
		 }
	 	 if(value4.equals("假")){
			  fp.setCuozhe(3); 
		 }  
	 	 
	 	 if(value5.equals("合格")){
	 		  fp.setLiyi(1); 
	 	 }
	 	 if(value5.equals("不合格")){
			  fp.setLiyi(2); 
		 }
	 	 if(value5.equals("假")){
			  fp.setLiyi(3); 
		 }  
	 	 
	 	 
	 	 
	 	 if(value6.equals("合格")){
	 		  fp.setZhuanbian(1); 
	 	 }
	 	 if(value6.equals("不合格")){
			  fp.setZhuanbian(2); 
		 }
	 	 if(value6.equals("假")){
			  fp.setZhuanbian(3); 
		 }  
	 	 
	 	 
	 	
	 	 if(value7.equals("假")){
			  fp.setIso9001(3); 
		 }else{
			 fp.setIso9001(Integer.parseInt(value7));
		 } 
	 	 
	 	 if(value8.equals("假")){
			  fp.setIso27001(3); 
		 }else{
			 fp.setIso27001(Integer.parseInt(value8));
		 } 
	 	 
	 	 
	 	 if(value9.equals("假")){
			  fp.setAnquan(3); 
		 }else{
			 fp.setAnquan(Integer.parseInt(value9));
		 } 
	 	 
	 	 if(value10.equals("假")){
			  fp.setCmmi(3); 
		 }else{
			 fp.setCmmi(Integer.parseInt(value10));
		 } 
	 	 
	 	 if(value11.equals("假")){
			  fp.setVss(3); 
		 }else{
			 fp.setVss(Integer.parseInt(value11));
		 } 
	 	 
	 	 
	 	 
	 	 if(value12.equals("合格")){
	 		  fp.setWord(1); 
	 	 }
	 	 if(value12.equals("不合格")){
			  fp.setWord(2); 
		 }
	 	 if(value12.equals("假")){
			  fp.setWord(3); 
		 }  
	 	 
	 	 if(value13.equals("合格")){
	 		  fp.setExcel(1); 
	 	 }
	 	 if(value13.equals("不合格")){
			  fp.setExcel(2); 
		 }
	 	 if(value13.equals("假")){
			  fp.setExcel(3); 
		 }  
	    	  
	 	 if(value14.equals("合格")){
			  fp.setPpt(1); 
		 }
		 if(value14.equals("不合格")){
			  fp.setPpt(2); 
		 }
		 if(value14.equals("假")){
			  fp.setPpt(3); 
		 }  
		 
		 if(value15.equals("合格")){
			  fp.setYanjiang(1); 
		 }
		 if(value15.equals("不合格")){
			  fp.setYanjiang(2); 
		 }
		 if(value15.equals("假")){
			  fp.setYanjiang(3); 
		 }  
		 
		 fp.setQingjia(Integer.parseInt(value16));
		 fp.setDiyiming(Integer.parseInt(value17));
		 fp.setGood(Integer.parseInt(value18));
		 fp.setExam(Double.parseDouble(value19));
		 fp.setEvaluation(value20);

		 	list.add(fp);
	    

	   }
			
	   System.out.println(sheet.getLastRowNum());
	   
	   
	   inputStream.close();  
	   workBook.close();//最后记得关闭工作簿  
	   for(int i = 0 ; i < list.size() ; i++) {
	   	System.out.println(list.get(i));
	   	}
		
 
		 return list;
		
	}
}
